/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package eproject_sem2;

import java.sql.*;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.Random;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JComboBox;

/**
 *
 * @author ZER0
 */
public class VotesSent_Sever {

    String user = ConnectSQL.user;
    String pass = ConnectSQL.pass;
    String ulr = ConnectSQL.ulr;
    String DriverClass = ConnectSQL.DriverClass;
    Connection con = null;

    public boolean Insert(String sWaybill, String sDate, String sEmployeeID, String sCustomersID, String sDeliveryAddress, String sGDistanceID, String sTypeMailerID, String sWeight, float sCostsIncurred, float sShippingfee, float sTotalamount) {
        boolean rsx = false;
        try {
            // add du lieu vao database
            Class.forName(DriverClass);
            con = DriverManager.getConnection(ulr, user, pass);
            String sql = "INSERT INTO VotesSent(Waybill,Date,EmployeeID,CustomersID,DeliveryAddress,GDistanceID,TypeParceID,GroupWeightID,CostsIncurred,ShippingFee,Totalamount) VALUES('" + sWaybill + "', '" + sDate + "', '" + sEmployeeID + "', '" + sCustomersID + "', '" + sDeliveryAddress + "', '" + sGDistanceID + "', '" + sTypeMailerID + "', '" + sWeight + "', " + sCostsIncurred + ", " + sShippingfee + ", " + sTotalamount + ")";
            PreparedStatement Psttm = con.prepareStatement(sql);
            Psttm.executeUpdate();
            rsx = true;
        } catch (SQLException ex) {
            Logger.getLogger(VotesSent_Sever.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(VotesSent_Sever.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            //kiem tra ham co gia tri de add ko ,neu ko co thi dong ket noi
            if (con != null) {
                try {
                    con.close();
                    System.out.println("Dong ket noi");
                } catch (Exception e) {
                }
            }
        }
        return rsx;
    }

    public boolean InsertCustomer(String cid, String Cname, String address, int Phone) {
        boolean rsx = false;
        try {
            // add du lieu vao database
            Class.forName(DriverClass);
            con = DriverManager.getConnection(ulr, user, pass);
            String sql1 = "INSERT INTO Customer(CustomersID,CustomerName,CustomerAddress,Phonenumber) VALUES('" + cid + "', '" + Cname + "', '" + address + "', " + Phone + ")";
            PreparedStatement Psttm = con.prepareStatement(sql1);
            Psttm.executeUpdate();
            rsx = true;
        } catch (SQLException ex) {
            Logger.getLogger(VotesSent_Sever.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(VotesSent_Sever.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            //kiem tra ham co gia tri de add ko ,neu ko co thi dong ket noi
            if (con != null) {
                try {
                    con.close();
                    System.out.println("Dong ket noi");
                } catch (Exception e) {
                }
            }
        }
        return rsx;
    }

    public void AddDatainJComboBox(String table, JComboBox sd) throws SQLException {
        try {
            Class.forName(DriverClass);
            con = DriverManager.getConnection(ulr, user, pass);
            String sql3 = "select * from " + table + "";
            Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ResultSet rs = st.executeQuery(sql3);

            while (rs.next()) {
                //lay du lieu do vao jcombobox
                String k = rs.getString(3);
                sd.addItem(k);
            }
        } catch (SQLException ex) {
            Logger.getLogger(VotesSent_Sever.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(VotesSent_Sever.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

    public float GetPrice(String CT, String table) throws ClassNotFoundException, SQLException {
        float kq = 0;
        try {
            Class.forName(DriverClass);
            con = DriverManager.getConnection(ulr, user, pass);
            String sql3 = "select * from " + table + "";
            Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ResultSet rs = st.executeQuery(sql3);
            while (rs.next()) {
                String k1 = rs.getString(3);
                if (k1.equalsIgnoreCase(CT)) {
                    kq = rs.getFloat(4);
                    break;
                }
            }


        } catch (SQLException ex) {
            Logger.getLogger(VotesSent_Sever.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(VotesSent_Sever.class.getName()).log(Level.SEVERE, null, ex);
        }
        return kq;
    }

    public float Getdistance(String t, String f, String table) throws ClassNotFoundException, SQLException {
        float kq = 0;
        float to = 0;
        float from = 0;
        try {
            Class.forName(DriverClass);
            con = DriverManager.getConnection(ulr, user, pass);
            String sql3 = "select * from " + table + "";
            Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ResultSet rs = st.executeQuery(sql3);
            while (rs.next()) {
                String k1 = rs.getString(3);
                if (k1.equalsIgnoreCase(t)) {
                    to = rs.getFloat(4);

                }
                if (k1.equalsIgnoreCase(f)) {
                    from = rs.getFloat(4);
                }
            }
            if (from >= to) {
                kq = from - to;
            } else {
                kq = to - from;
            }
        } catch (SQLException ex) {
            Logger.getLogger(VotesSent_Sever.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(VotesSent_Sever.class.getName()).log(Level.SEVERE, null, ex);
        }
        return kq;
    }

    public float GetPriceDistance(float p, String table) throws ClassNotFoundException, SQLException {
        float kq = 0;
        try {
            Class.forName(DriverClass);
            con = DriverManager.getConnection(ulr, user, pass);
            String sql3 = "select * from " + table + "";
            Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ResultSet rs = st.executeQuery(sql3);
            while (rs.next()) {
                int i = 0;
                String[] temp = new String[2];
                String k1 = rs.getString(3);
                temp = k1.split("-");
                float k2 = Float.parseFloat(temp[i]);
                float k3 = Float.parseFloat(temp[i + 1]);
                if (p >= k2) {
                    if (p < k3) {
                        kq = rs.getFloat(4);
                        break;
                    }
                } else {
                    k2 = 0;
                    k3 = 0;
                    i = i + 2;
                    temp = null;
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(VotesSent_Sever.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(VotesSent_Sever.class.getName()).log(Level.SEVERE, null, ex);
        }
        return kq;
    }

    public float Shippingfee(float Pdistance, float PTypeParce, float Weight) {
        float kq = 0;
        try {
            kq = Pdistance *Weight + PTypeParce;
        } catch (Exception ez) {
        }
        return kq;

    }

    public float Totalamount(double Shippingfee, float CostsIncurred) {
        float kq = 0;
        try {
            kq = (float) (Shippingfee + CostsIncurred);
        } catch (Exception ez) {
        }
        return kq;
    }

    public int CheckID(String CT, String table) throws ClassNotFoundException, SQLException {
        int kq = 1;
        try {
            Class.forName(DriverClass);
            con = DriverManager.getConnection(ulr, user, pass);
            String sql3 = "select * from " + table + "";
            Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ResultSet rs1 = st.executeQuery(sql3);
            while (rs1.next()) {
                String k1 = rs1.getString(2);
                if (k1.equalsIgnoreCase(CT)) {
                    kq = -1;
                    break;
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(VotesSent_Sever.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(VotesSent_Sever.class.getName()).log(Level.SEVERE, null, ex);
        }
        return kq;
    }
    public String GetDate() {
        Date todayD = new Date(System.currentTimeMillis());//lay gio he thong
        SimpleDateFormat dayFormat = new SimpleDateFormat("dd/MM/yyyy");//khai bao dinh dang hien thi
        String todayS = dayFormat.format(todayD.getTime());//dinh dang lai ngay ,chuyen thong tin sang kieu chuoi
        return todayS;
    }

    public String CreateID(String table, String tt) {
        String CID = null;
        int k1 = 0;
        try {
            Class.forName(DriverClass);
            con = DriverManager.getConnection(ulr, user, pass);
            String sql3 = "select * from " + table + "";
            Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ResultSet rs = st.executeQuery(sql3);
            while (rs.next()) {
                if (k1 < rs.getInt(1)) {
                    k1 = rs.getInt(1);
                }
            }
            k1 = k1 + 1;
            NumberFormat formatter = new DecimalFormat("0000");
            String k2 = formatter.format(k1);
            CID = "" + tt + "" + k2;
        } catch (SQLException ex) {
            Logger.getLogger(VotesSent_Sever.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(VotesSent_Sever.class.getName()).log(Level.SEVERE, null, ex);
        }
        return CID;

    }

    public String GetID(String u, String table) {
        String rs1 = null;
        try {
            Class.forName(DriverClass);
            con = DriverManager.getConnection(ulr, user, pass);
            String sql3 = "select * from " + table + "";
            Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ResultSet rs = st.executeQuery(sql3);
            while (rs.next()) {
                String k1 = rs.getString(3);
                if (k1.equalsIgnoreCase(u)) {
                    rs1 = rs.getString(2);
                    break;
                }
            }

        } catch (SQLException ex) {
            Logger.getLogger(VotesSent_Sever.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(VotesSent_Sever.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rs1;
    }

    public String GetIDD(float u, String table) {
        String rs1 = null;
        try {
            Class.forName(DriverClass);
            con = DriverManager.getConnection(ulr, user, pass);
            String sql3 = "select * from " + table + "";
            Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ResultSet rs = st.executeQuery(sql3);
            while (rs.next()) {
                int i = 0;
                String[] temp = new String[2];
                String k1 = rs.getString(3);
                temp = k1.split("-");
                float k2 = Float.parseFloat(temp[i]);
                float k3 = Float.parseFloat(temp[i + 1]);
                if (u >= k2) {
                    if (u < k3) {
                        rs1 = rs.getString(2);
                        break;
                    }
                } else {
                    k2 = 0;
                    k3 = 0;
                    i = i + 2;
                    temp = null;
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(VotesSent_Sever.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(VotesSent_Sever.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rs1;
    }
}
